/*----------------------------------------------------------
 * FILE: JdbcHelper.java
 * LOGICAL NAME: JdbcHelper
 * PRODUCT: bila-framework
 *----------------------------------------------------------
 * MODIFICATIONS
 * DATE Oct 10, 2011
 * AUTHOR wjuarez
 *----------------------------------------------------------
 */
package bila.framework.jdbc;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Calendar;
import java.util.List;

import javax.sql.DataSource;

import org.apache.commons.beanutils.DynaBean;
import org.apache.commons.beanutils.RowSetDynaClass;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @author WJuarez
 * <b>Created by </b>wjuarez
 * <b>for</b> bila-framework
 * <b>package</b> bila.framework.jdbc
 * <b>on</b> Oct 10, 2011 5:46:55 PM
 * <b>Purpose</b> 
 * <p>
 *		Metodos para el manejo de base de datos utilizando el API JDBC
 * </p>
 */
public class JdbcHelper {

    private Log log = LogFactory.getLog(JdbcHelper.class);
    private DataSource dataSource;
    private Connection conn;

    /**
     * Cierra la conexion a la base de datos 
     */
    public void disconnect() {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
                try {
                    if (!conn.isClosed()) {
                        conn.close();
                    }
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            }
        }
    }

    /**
     * Abre la conexion a la base de datos
     * @throws SQLException
     */
    public void openConnection() throws SQLException {
        if (conn == null)
            conn = dataSource.getConnection();
        else if (conn.isClosed())
            conn = dataSource.getConnection();
    }

    /**
     * Ejecuta una funcion DML en la base de datos
     * @param sql Sentencia a ejecutar
     * @param params Parametros de la sentencia
     * @throws Exception
     */
    public void executeDml(String sql, Object[] params) throws Exception {
        try {
            openConnection();
            PreparedStatement pstmt = getPreparedStatement(sql, params);
            pstmt.executeUpdate();
            pstmt.close();
            disconnect();
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e);
            disconnect();
            throw e;
        }
    }

    /**
     * Ejecuta un query en la base
     * @param query Sentencia a ejecutar
     * @param params Parametros de la sentencia
     * @return Objeto de tipo List con los registros recuperados
     * @throws Exception
     */
    @SuppressWarnings("unchecked")
    public List<DynaBean> getByQuery(String query, Object[] params) throws Exception {
        try {
            openConnection();
            RowSetDynaClass rowset = null;
            PreparedStatement pstmt = getPreparedStatement(query, params);
            ResultSet rs = pstmt.executeQuery();
            rowset = new RowSetDynaClass(rs);
            rs.close();
            pstmt.close();
            disconnect();
            return rowset.getRows();
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e);
            disconnect();
            throw e;
        }
    }

    /**
     * Retorna una coneccion a la base de datos
     * @return Objeto de tipo Connection
     * @throws Exception
     */
    public Connection getConnection() throws Exception {
        return dataSource.getConnection();
    }

    private PreparedStatement getPreparedStatement(String sql, Object[] params) throws SQLException {
        PreparedStatement pstmt = conn.prepareStatement(sql);
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                Object param = params[i];
                pstmt.setObject(i + 1, param, resolveType(param));
            }
        }
        return pstmt;
    }

    @SuppressWarnings("rawtypes")
    private int resolveType(Object param) {
        int sqlType = Types.VARCHAR;
        if (param == null) {
            sqlType = Types.NULL;
        } else {
            Class paramClass = param.getClass();
            if (param instanceof String) {
                sqlType = Types.VARCHAR;
            } else if (paramClass.equals(double.class) || param instanceof Double) {
                sqlType = Types.DOUBLE;
            } else if (param instanceof BigDecimal) {
                sqlType = Types.NUMERIC;
            } else if (param instanceof Calendar) {
                sqlType = Types.DATE;
            } else if (paramClass.equals(int.class) || param instanceof Integer) {
                sqlType = Types.INTEGER;
            } else if (paramClass.equals(long.class) || param instanceof Long) {
                sqlType = Types.BIGINT;
            } else if (paramClass.equals(float.class) || param instanceof Float) {
                sqlType = Types.REAL;
            }
        }
        return sqlType;
    }

    public String tryConnection(String user, String password) throws SQLException {
        Connection con = null;
        try {
            con = dataSource.getConnection(user.toUpperCase(), password.toUpperCase());
            con.close();
        } catch (Exception e) {
            return e.getMessage();
        } finally {
            con.close();
        }
        return "";
    }

    /**
     * Ejecuta una funcion de la base
     * @param sql Funcion a ejecutar
     * @param params Parametros de la funci�n
     * @return Objeto que la funcion retorna
     * @throws SQLException
     */
    public Object executeFunction(String sql, Object[] params) throws Exception {
        CallableStatement cstmt = null;
        Object outPut;
        try {
            openConnection();
            cstmt = this.conn.prepareCall(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    Object param = params[i];
                    if (i == 0) {
                        cstmt.registerOutParameter(1, resolveType(param));
                    } else {
                        cstmt.setObject(i + 1, param, resolveType(param));
                    }
                }
            }
            cstmt.execute();
            outPut = cstmt.getObject(1);
        } finally {
            cstmt.close();
            conn.close();
            cstmt = null;
            conn = null;
        }
        return outPut;
    }

    /**
     * Ejecuta un procedimiento almacenado de la base de datos
     * @param sql Nombre del procedimiento
     * @param params Parametros del procedimiento
     * @throws SQLException
     */
    public void executeProcedure(String sql, Object[] params)
            throws SQLException {
        CallableStatement cstmt = null;
        try {
            openConnection();
            cstmt = conn.prepareCall(sql);
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    Object param = params[i];
                    cstmt.setObject(i + 1, param, resolveType(param));
                }
            }
            cstmt.execute();
        } finally {
            cstmt.close();
            conn.close();
            cstmt = null;
            conn = null;
        }
    }

    /**
     * Convierte a XML el query a ejecutar
     * @param query
     * @param params
     * @return String con el xml generado
     * @throws Exception
     */
    public String getXmlQuery(String query, Object[] params) throws Exception {
        try {
            openConnection();
            PreparedStatement pstmt = getPreparedStatement(query, params);
            ResultSet rs = pstmt.executeQuery();
            XmlResultSet xmlRs = new XmlResultSet(rs);
            rs.close();
            pstmt.close();
            disconnect();
            return xmlRs.getXml();
        } catch (Exception e) {
            log.error("Error en getXmlQuery: " + e.getMessage());
            disconnect();
            throw e;
        }
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
}
